package cn.com.surker.oa.service.impl;

import cn.com.surker.common.Code;
import cn.com.surker.database.JDBCTemplateCache;
import cn.com.surker.enums.StateEnum;
import cn.com.surker.erp.entity.material.ErpSupplier;
import cn.com.surker.material.entity.MaterialStorageItem;
import cn.com.surker.oa.dao.OaSupplierDao;
import cn.com.surker.oa.dao.OaSupplierOrgDao;
import cn.com.surker.oa.entity.OaSupplier;
import cn.com.surker.oa.entity.OaSupplierDetail;
import cn.com.surker.oa.entity.OaSupplierOrg;
import cn.com.surker.oa.service.OaSupplierService;
import cn.com.surker.oa.vo.OaSupplierVo;
import cn.com.surker.system.dao.DataSourceInfoDao;
import cn.com.surker.system.dao.SysOriginDao;
import cn.com.surker.system.dao.SysOriginRoleDao;
import cn.com.surker.system.entity.DataSourceInfo;
import cn.com.surker.system.entity.SysOrigin;
import cn.com.surker.system.entity.SysOriginRole;
import cn.com.surker.util.DateUtil;
import cn.com.surker.util.StringUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.stream.Collectors;

/**
* <p>
    * 供应商 服务实现类
    * </p>
*
* @author admin
* @since 2022-10-05
*/
@Service
@Slf4j
public class OaSupplierServiceImpl extends ServiceImpl<OaSupplierDao, OaSupplier> implements OaSupplierService {

    @Resource
    private OaSupplierDao oaSupplierDao;
    @Resource
    private SysOriginDao originDao;
    @Resource
    private DataSourceInfoDao dsDao;
    @Resource
    private SysOriginRoleDao originRoleDao;
	@Resource
	private OaSupplierOrgDao oaSupplierOrgDao;

	@Override
	public List<OaSupplier> list() {
		QueryWrapper<OaSupplier> qw = new QueryWrapper<>();
		qw.eq("status", StateEnum.ENABLED.getCode());
		List<OaSupplier> oaSuppliers = oaSupplierDao.selectList(qw);
		return oaSuppliers;
	}

	/**
     * 根据关联公司查询供应商信息
     * @return
     */
    public List<OaSupplier> queryByOrg() {
        return oaSupplierDao.queryByOrg();
    }

	/**
	 * 查询关联供应商的信息
	 * @param type
	 * @param supplierId
	 * @return
	 */
	public List<Map<String, Object>> findRelationSupplier(Integer type, Long supplierId) {
		if (StringUtils.isAllParamNull(type, supplierId))
			return null;

		return oaSupplierDao.findRelationSupplier(type, supplierId);
	}
	/**
	 * 分页查询供应商含关联类型、公司
	 * @param searchVal 搜索条件
	 * @param offset 分页起始位置
	 * @param limit 每页大小
	 * @return
	 */
	public IPage queryByPageV2(String searchVal, Long orgId, String types, Integer offset, Integer limit) {
		if (StringUtils.isNull(offset))
			offset = 0;
		if (StringUtils.isNull(limit))
			limit = 15;
		PageHelper.offsetPage(offset, limit);
		QueryWrapper<OaSupplier> query = new QueryWrapper<>();
		query.eq("a.status",0);
		if (!StringUtils.isNull(orgId))
			query.eq("b.org_id", orgId);
		if (!StringUtils.isNull(types)) {
			String[] type = types.split(",");
			query.in("b.type", type);
		}
		if (!StringUtils.isNull(searchVal))
			query.and(orderWrapper -> orderWrapper.like("a.name", searchVal).or()
					.like("a.full_name", searchVal).or().like("a.code", searchVal));
		query.groupBy("a.id");
		query.orderByDesc("a.create_time", "a.name", "a.code");
		IPage<OaSupplierVo> iPage = oaSupplierDao.findOrgPage(new Page<>((offset + limit) / limit, limit), query);
		/**分页插件**/
		if (!StringUtils.isNull(iPage.getRecords())) {
			PageInfo pageInfo = new PageInfo(iPage.getRecords());
			if (!StringUtils.isNull(pageInfo.getTotal()))
				iPage.setTotal(pageInfo.getTotal());
		}
		return iPage;
	}

	/**
	 * 保存 / 取消 关联公司
	 * @param supplierId
	 * @param orgId
	 * @param type
	 * @return
	 */
	public Integer saveRelation(Long supplierId, Long orgId, Integer type, Integer status, Long updateId) {
		if (StringUtils.isAllParamNull(supplierId, orgId, type, status))
			return -1;
		Date nowDate = new Date();
		if (status == 0) {
			QueryWrapper<OaSupplierOrg> query = new QueryWrapper<>();
			query.eq("supplier_id", supplierId);
			query.eq("org_id", orgId);
			query.eq("type", type);
			query.eq("status", 0);
			List<OaSupplierOrg> list = oaSupplierOrgDao.selectList(query);
			if (list != null && list.size() > 0)
				return 0;
			SysOrigin origin = originDao.selectById(orgId);
			if (origin == null || origin.getStatus().intValue() == 1)
				return -1;
			DataSourceInfo ds = dsDao.queryDSByOriginId(orgId);
			if (ds == null || ds.getStatus().intValue() == 1) {
				OaSupplierOrg supplierOrg = new OaSupplierOrg(supplierId, orgId, origin.getOriginName(), null, type, status, nowDate, updateId, null, null, null);
				Integer code = oaSupplierOrgDao.insert(supplierOrg);
				if (code > 0)
					return 0;
			} else {
				OaSupplierOrg supplierOrg = new OaSupplierOrg(supplierId, orgId, origin.getOriginName(), ds.getId(), type, status, nowDate, updateId, null, null, null);
				Integer code = oaSupplierOrgDao.insert(supplierOrg);
				if (code > 0)
					return 0;
			}
		} else if (status == 1) {
			UpdateWrapper<OaSupplierOrg> updateWrapper = new UpdateWrapper<>();
			updateWrapper.eq("supplier_id", supplierId);
			updateWrapper.eq("org_id", orgId);
			updateWrapper.eq("type", type);
			updateWrapper.eq("status", 0);
			updateWrapper.set("status", status);
			updateWrapper.set("updater", updateId);
			updateWrapper.set("update_time", nowDate);
			Integer code = oaSupplierOrgDao.update(new OaSupplierOrg(), updateWrapper);
			if (code > 0)
				return 0;
		}
		return -1;
	}

	/**
	 * 根据关联公司查询供应商信息
	 * @return
	 */
	public List<OaSupplier> queryByOrgV1(Long orgId) {

		return oaSupplierDao.queryByOrgV1(orgId, null);
	}

	/**
	 * 查询材料合同关联供应商
	 * @param contractId
	 * @return
	 */
	public List<OaSupplier> queryByContractId(Long orgId, Long contractId) {
		if (StringUtils.isAllParamNull(orgId,contractId))
			return null;
		QueryWrapper<OaSupplier> query = new QueryWrapper<>();
		query.eq("status", 0);
		query.inSql("id", "select supplier_id from main_material_contract_price " +
				" where org_id = " + orgId + " and status = 0 and contract_id = " + contractId);
		List<OaSupplier> supplierList = oaSupplierDao.selectList(query);
		if (supplierList == null || supplierList.size() == 0)
			return null;
		return supplierList;
	}
	/**
	 * 分页查询供应商
	 * @param searchVal 搜索条件
	 * @param status 状态(0:启用1:禁用)
	 * @param offset 分页起始位置
	 * @param limit 每页大小
	 * @return
	 */
	public IPage queryByPageV1(String searchVal, Long orgId, Integer status, Integer type,
							   Integer offset, Integer limit) {
		if (StringUtils.isNull(offset))
			offset = 0;
		if (StringUtils.isNull(limit))
			limit = 15;
		PageHelper.offsetPage(offset, limit);
		QueryWrapper<OaSupplier> query = new QueryWrapper<>();
		if (!StringUtils.isNull(status))
			query.eq("b.status", status);
		else
			query.eq("b.status", 0);

		/*query.eq("b.status", StateEnum.ENABLED.getCode());*/
		if (!StringUtils.isNull(orgId))
			query.eq("b.org_id", orgId);
		if (!StringUtils.isNull(type))
			query.eq("b.type", type);
		if (!StringUtils.isNull(searchVal))
			query.and(orderWrapper -> orderWrapper.like("a.name", searchVal).or()
					.like("a.full_name", searchVal).or().like("a.code", searchVal));
		query.orderByDesc("a.create_time", "a.name", "a.code");
		query.groupBy("a.code", "a.full_name", "b.type");
		IPage<OaSupplier> iPage = oaSupplierDao.selectDetailPage(new Page<>((offset + limit) / limit, limit), query);
		/**分页插件**/
		if (iPage != null && iPage.getRecords() != null && iPage.getRecords().size() > 0) {
			PageInfo pageInfo = new PageInfo(iPage.getRecords());
			if (!StringUtils.isNull(pageInfo.getTotal()))
				iPage.setTotal(pageInfo.getTotal());
			/**获取所有用户信息**/
			List<OaSupplier> list = iPage.getRecords();
			List<OaSupplier> resultList = new ArrayList<>();
			for (Integer i = 0; i < list.size(); i++) {
				OaSupplier supplier = list.get(i);
				/**查询所属部门**/
				List<Map<String, Object>> orgList = oaSupplierDao.findRelationOrg(supplier.getId(), supplier.getType());
				if (orgList != null && orgList.size() > 0) {
					Boolean slat = true;
					for (Map<String, Object> map : orgList) {
						if (slat) {
							slat = false;
							supplier.setOrgName(map.get("orgName") + ",");
						} else {
							supplier.setOrgName(supplier.getOrgName() + map.get("orgName") + ",");
						}
					}
					supplier.setOrgName(supplier.getOrgName().substring(0, supplier.getOrgName().length() - 1));
				}
				resultList.add(supplier);
			}
			iPage.setRecords(resultList);
		}
		return iPage;
	}
    /**
     * 分页查询供应商
     * @param searchVal 搜索条件
     * @param status 状态(0:启用1:禁用)
     * @param offset 分页起始位置
     * @param limit 每页大小
     * @return
     */
    public IPage queryByPage(String searchVal, Long orgId, Integer status, Integer type, Integer offset, Integer limit) {
        if (StringUtils.isNull(offset))
            offset = 0;
        if (StringUtils.isNull(limit))
            limit = 15;
        PageHelper.offsetPage(offset, limit);
        QueryWrapper<OaSupplier> query = new QueryWrapper<>();
        if (!StringUtils.isNull(status))
			query.eq("b.status", status);
        else
			query.eq("b.status", 0);

		/*query.eq("b.status", StateEnum.ENABLED.getCode());*/
        if (!StringUtils.isNull(orgId))
			query.eq("b.org_id", orgId);
        if (!StringUtils.isNull(type))
			query.eq("b.type", type);
        if (!StringUtils.isNull(searchVal))
			query.and(orderWrapper -> orderWrapper.like("a.name", searchVal).or()
				.like("a.full_name", searchVal).or().like("a.code", searchVal));
		query.orderByDesc("a.create_time", "a.name", "a.code");
		IPage<OaSupplier> iPage = oaSupplierDao.selectDetailPage(new Page<>((offset + limit) / limit, limit), query);
        /**分页插件**/
        if (!StringUtils.isNull(iPage.getRecords())) {
            PageInfo pageInfo = new PageInfo(iPage.getRecords());
            if (!StringUtils.isNull(pageInfo.getTotal()))
                iPage.setTotal(pageInfo.getTotal());
        }
        return iPage;
    }

    /**
     * 同步材料供应商
     */
    public void synMaterialSupplier() {
        QueryWrapper<DataSourceInfo> query = new QueryWrapper<>();
        query.eq("status", 0);
        query.eq("type", 1);
		query.eq("biz_type", 1);
        List<DataSourceInfo> dsList = dsDao.selectList(query);
        if (dsList == null || dsList.size() == 0)
            return;

        String sql = "select ID,U_Name,TaxCode,CONVERT(varchar(100), creationTime, 20) creationTime " +
                " from material_supplier_t where isvalid = 1";
        for (int i = 0; i < dsList.size(); i++) {
            DataSourceInfo ds = dsList.get(i);
            JdbcTemplate jdbcTemplate = JDBCTemplateCache.getJdbcTemplate(ds);/**获取数据库联系信息**/
            List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql);
            if (listMap == null || listMap.size() == 0)
                continue;

            SysOrigin origin = originDao.queryByDsId(ds.getId());
            if (origin == null)
                continue;
            for (int j = 0; j < listMap.size(); j++) {
                Map<String, Object> map = listMap.get(j);
                Integer id = StringUtils.convertInteger(map.get("ID"));
                String name = StringUtils.convertString(map.get("U_Name"));
                String code = StringUtils.convertString(map.get("TaxCode"));
                String time = StringUtils.convertString(map.get("creationTime"));
                OaSupplier supplier = oaSupplierDao.queryErpInfo(origin.getId(), id, code, name);
                if (supplier == null) {
                    supplier = new OaSupplier(name, name, "", "", 0, "", 0, "", origin.getId(), origin.getOriginName(),
                            code, id, DateUtil.convertDate(time));
                    oaSupplierDao.insert(supplier);
                } else {//如果存在则修改
                    supplier.setFullName(name);
                    supplier.setName(name);
                    supplier.setCode(code);
                    supplier.setOrgName(origin.getOriginName());
                    supplier.setCreateTime(DateUtil.convertDate(time));
                    oaSupplierDao.updateById(supplier);
                }
            }

        }
    }

    /**
     *
     * @param code 供应商code码
     * @param name 简称
     * @param fullName 全称
     * @param legal 法人代表
     * @param orgId 工厂ID
     * @param orgName 工厂名称
     * @param phone 联系方式
     * @param type 类型(0:材料1:生产)
     * @param address 公司地址
     * @return
     */
    public Integer saveSupplier(String code, String name, String fullName, String legal, Long orgId, String orgName,
                         String phone, Integer type, String address, String remark,Double initialAmount, Date initialDate) {
        if (StringUtils.isNull(code))
            return Code.NO_SUPPLIER_CODE_EMPTY.getCode();
        if (StringUtils.isAllParamNull(name, fullName))
            return Code.NO_SUPPLIER_NAME_EMPTY.getCode();
        if (StringUtils.isAllParamNull(orgId, orgName))
            return Code.NO_SUPPLIER_ORIGIN_EMPTY.getCode();
        if (StringUtils.isNull(type))//如果为空默认为材料供应商
            type = 0;

        code = code.replace(" ", "");
        name = name.replaceAll(" ", "").replaceAll("（", "(").replaceAll("）", ")");
        fullName = fullName.replaceAll(" ", "").replaceAll("（", "(").replaceAll("）", ")");
        Integer total = oaSupplierDao.queryExistInfo(orgId, code, name);
        if (total > 0)
            return Code.NO_SUPPLIER_CODE_EXIST.getCode();

        OaSupplier supplier = new OaSupplier(name, fullName, legal, phone, type, address, 0, remark, orgId,
                orgName, code, null, new Date());

        Integer resultCode = oaSupplierDao.insert(supplier);
        if (resultCode > 0)
            return 0;

        return -1;
    }

    /**
     * 修改供应商
     * @param id 记录ID
     * @param code 供应商code码
     * @param name 简称
     * @param fullName 全称
     * @param legal 法人代表
     * @param phone 联系方式
     * @param type 类型(0:材料1:生产)
     * @param address 公司地址
     * @param status 状态(0:启用1:禁用)
     * @param initialAmount
	 * @param initialDate
	 * @return
     */
    public Integer updateSupplier(Long id, String code, String name, String fullName, String legal,
								  String phone, Integer type, String address, String remark, Integer status, Double initialAmount, Date initialDate, Long uid) {
        if (StringUtils.isNull(id))
            return Code.NO_SUPPLIER_NOT.getCode();
        OaSupplier supplier = oaSupplierDao.selectById(id);
        if (supplier == null)
            return Code.NO_SUPPLIER_NOT.getCode();
        QueryWrapper<SysOriginRole> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("org_id", supplier.getOrgId());
        queryWrapper.eq("uid", uid);
        queryWrapper.eq("status", 0);
        Integer total = originRoleDao.selectCount(queryWrapper);
        if (total < 1)
            return Code.NO_EDIT_ROLE.getCode();


        if (!StringUtils.isNull(code)) {
            code = code.replace(" ", "");
            supplier.setCode(code);
        }
        if (!StringUtils.isNull(name)) {
            name = name.replaceAll(" ", "").replaceAll("（", "(").replaceAll("）", ")");
            supplier.setName(name);
        }
        if (!StringUtils.isNull(fullName)) {
            fullName = fullName.replaceAll(" ", "").replaceAll("（", "(").replaceAll("）", ")");
            supplier.setFullName(fullName);
        }
        if (!StringUtils.isNull(legal))
            supplier.setLegal(legal);
        if (!StringUtils.isNull(phone))
            supplier.setPhone(phone);
        if (!StringUtils.isNull(type))
            supplier.setType(type);
        if (!StringUtils.isNull(address))
            supplier.setAddress(address);
        if (!StringUtils.isNull(remark))
            supplier.setRemark(remark);
        if (!StringUtils.isNull(status)) {
			if (status == 1) {
				supplier.setStatus(status);

			}
		}
		if (initialAmount != null){
			supplier.setInitialAmount(initialAmount);
		}
		if (initialDate != null){
			supplier.setInitialDate(initialDate);
		}

        supplier.setCreateTime(new Date());
        Integer resultCode = oaSupplierDao.updateById(supplier);
        if (resultCode > 0)
            return 0;

        return -1;
    }


	@Override
	public void syncErp2() {
		List<OaSupplierDetail> detailList = oaSupplierDao.findDetailList();
		//	根据数据分组
		Map<Long, List<OaSupplierDetail>> orgMaps = detailList.stream().filter(e -> {
			Long orgId = e.getOrgId();
			if (orgId == null) {
				return false;
			} else {
				return true;
			}
		}).collect(Collectors.groupingBy(OaSupplier::getOrgId));
		//	查询每个站的数据
		Set<Long> orgs = orgMaps.keySet();
		for (Long orgId : orgs) {
			//	查询数据源
			DataSourceInfo ds = dsDao.queryDSByOriginId(orgId);
			if (ds == null) {
				continue;
			}
			//	获取oa中的数据id
			List<OaSupplierDetail> orgSupplierList = orgMaps.get(orgId);
			//	查询erp中的数据
			JdbcTemplate localJdbcTemplate = JDBCTemplateCache.getLocalJdbcTemplate(ds);
			/*List<ErpSupplier> erpSupplierList = localJdbcTemplate.query("select u_name uName,Abbreviation abbreviation,U_Mode uMode,TaxCode taxCode from material_supplier_t where" +
				" isvalid = 1", BeanPropertyRowMapper.newInstance(ErpSupplier.class));*/
			/*if (erpSupplierList == null || erpSupplierList.isEmpty()){
				//	没有数据,需要全部插入
			}else {
				//	存在数据,需要过滤
			}*/
			try {
				for (int i = 0, s = orgSupplierList.size(); i < s; i++) {
					OaSupplierDetail oaSupplierDetail = orgSupplierList.get(i);
					//	供应商id
					Long supplierId = oaSupplierDetail.getId();
					//	供应商码
					String code = oaSupplierDetail.getCode();
					//	类型
					Integer type = oaSupplierDetail.getType();
					//	供应商名
					String name = oaSupplierDetail.getName();
					//	供应商全程
					String fullName = oaSupplierDetail.getFullName();
					//	查询erp中是否存在
					ErpSupplier erpSupplier = null;
					try {
						List<ErpSupplier> list = localJdbcTemplate.query("select id,GroupID groupId from material_supplier_t where" +
							" isvalid = 1 and U_Mode = " + type + " and taxCode = '" + code + "'", BeanPropertyRowMapper.newInstance(ErpSupplier.class));
						if (list != null && list.size() > 0) {
							erpSupplier = list.get(0);
						}
					} catch (DataAccessException e) {
						e.printStackTrace();
					}
					if (erpSupplier == null) {
						//	没有数据,直接新增
						String sql = "insert into material_supplier_t (GroupID,u_code,u_name, Abbreviation, U_Mode,taxCode,Sys_Dept_ID,Property,Status,isvalid) " +
							" values (?,?,?,?,?,?,?,?,?,1)";
						Object[] erpParams = new Object[]{supplierId, code, fullName, fullName, type, code, 1, 1, 2};
						int update = localJdbcTemplate.update(sql, erpParams);
					} else {
						// 存在数据
						///		判断是否是添加了oaid的的
						Long groupId = erpSupplier.getGroupId();
						if (groupId == null || !groupId.equals(supplierId)) {
							Long erpId = erpSupplier.getId();
							//	没有过id,需要更新
							String sql = "update material_supplier_t set GroupID = ? where id = " + erpId;
							Object[] erpParams = new Object[]{supplierId};
							int update = localJdbcTemplate.update(sql, erpParams);
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		log.error("同步到erp供应商完成");
	}

	@Override
	public OaSupplier queryOaDataByCode(String code) {
		OaSupplier oaSupplier = oaSupplierDao.queryOaData(code, null);
		return oaSupplier;
	}

	public OaSupplier queryOaDataByName(String name) {
		OaSupplier oaSupplier = oaSupplierDao.queryOaData(null, name);
		return oaSupplier;
	}

	public OaSupplier queryOaDataByCodeAndName(String code, String name) {
		OaSupplier oaSupplier = oaSupplierDao.queryOaData(code, name);
		return oaSupplier;
	}


	/**
	 * 同步供应商联系人
	 */
	public void synLink() {
    	List<Map<String, Object>> linkList = oaSupplierDao.findLinkMan();
    	if (linkList == null || linkList.size() == 0)
    		return;
    	Map<Long, List<Map<String, Object>>> map = new HashMap<>();
    	for (int i = 0; i < linkList.size(); i++) {
			Map<String, Object> temp = linkList.get(i);
			if (StringUtils.isNull(temp.get("id")))
				continue;
			Long dsId = StringUtils.convertLong(temp.get("id"));
			if (map.containsKey(dsId)) {
				map.get(dsId).add(temp);
				map.put(dsId, map.get(dsId));
			} else {
				List<Map<String, Object>> tempList = new ArrayList<>();
				tempList.add(temp);
				map.put(dsId, tempList);
			}
		}
    	for (Long key : map.keySet()) {
    		try {
				DataSourceInfo ds = dsDao.selectById(key);
				JdbcTemplate localJdbcTemplate = JDBCTemplateCache.getLocalJdbcTemplate(ds);
				if (map.get(key) == null || map.get(key).size() == 0)
					continue;
				for (int i = 0; i < map.get(key).size(); i++) {
					Map<String, Object> temp = map.get(key).get(i);
					String sql = "update material_supplier_t set remarks='"  + temp.get("link_man") + "',contacts = '" + temp.get("link_man")
							+ "' where GroupID = " + temp.get("sid");
					localJdbcTemplate.update(sql);
					System.out.println("----------sql------------" + sql);
				}
			} catch (Exception e) {
    			System.out.println("ds-------------" + key);
    			continue;
			}

		}
	}

	private static void syncOldData() {
		//	查询旧的oa数据
		String url = "jdbc:mysql://47.108.113.248:3306/cloud?serverTimezone=UTC&characterEncoding=utf8&serverTimezone=GMT%2b8&rewriteBatchedStatements=true";
		String username = "root";
		String password = "chixu_hnt@2018";

		try {
			// 加载驱动程序
			Class.forName("com.mysql.cj.jdbc.Driver");

			// 连接到数据库
			Connection conn = DriverManager.getConnection(url, username, password);
			// 创建Statement对象
			Statement stmt = conn.createStatement();
			//	查询数据库中旧的oa的数据(0:材料，1：生产)
			ResultSet rs = stmt.executeQuery("SELECT * FROM material_storage_item where org_id = 130");
			//	处理数据
			List<MaterialStorageItem> storageItems = new ArrayList<>();
			while (rs.next()) {
				//	唯一码
				long id = rs.getLong("id");
				BigDecimal totalPrice = rs.getBigDecimal("total_price");
				BigDecimal taxTotalPrice = rs.getBigDecimal("tax_total_price");
				BigDecimal count = rs.getBigDecimal("count");
				MaterialStorageItem storageItem = new MaterialStorageItem();
				storageItem.setId(id);
				storageItem.setTotalPrice(totalPrice);
				storageItem.setTaxTotalPrice(taxTotalPrice);
				storageItem.setCount(count);
				storageItems.add(storageItem);
			}
			for (MaterialStorageItem storageItem : storageItems) {
				//	计算单价
				BigDecimal totalPrice = storageItem.getTotalPrice();
				BigDecimal count = storageItem.getCount();
				if (count.doubleValue() == 0d) {
					continue;
				}
				BigDecimal up = totalPrice.divide(count, 2, BigDecimal.ROUND_HALF_UP);
				storageItem.setUnitPrice(up);
				storageItem.setTaxUnitPrice(up);
				String updateSql = "update material_storage_item set unit_price = " + up + ",tax_unit_price = " + up + " where id = " + storageItem.getId();
				stmt.executeUpdate(updateSql);
			}

			// 关闭连接和结果集
			rs.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		//	syncOldData();
	}

}
